﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using UncleBobWebService.Models;
using UncleBobWebService.ServiceRequester;


namespace UncleBobWebService.Database
{
    class StaffDBAdapter : IDBAdapter
    {

        #region "Overrides"

        public override Message CreateData(Message msg, object data)
        {
            mCommand = mConnection.CreateCommand();

            mCommand.CommandText = string.Format("INSERT INTO Jobs (JobTitle, JobLocation, JobSalary, JobDescription) VALUES ('{0}', '{1}', '{2}', '{3}')"             
                    , ((JobModel)data).Title
                    , ((JobModel)data).Location
                    , ((JobModel)data).Salary
                    , ((JobModel)data).Description);

            try
            {
                mConnection.Open();
                mCommand.ExecuteNonQuery();
                mConnection.Close();
                msg.Result = true;
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }

            return msg;
        }


        public override Message SearchForSpecificData(Message msg, Object data)
        {
            JobModel newJob = new JobModel();
            msg = new Message();

            mTable = new DataTable();
            mCommand = mConnection.CreateCommand();
            mCommand.CommandText = string.Format("SELECT * FROM Jobs WHERE JobTitle = '{0}'", ((JobModel)data).Title);
            SqlDataAdapter mAdapter = new SqlDataAdapter(mCommand);

            try
            {
                mConnection.Open();
                mAdapter.Fill(mTable);
                mConnection.Close();

                if (mTable.Rows.Count != 0)
                {

                    DataRow row = mTable.Rows[0];

                    JobModel job = new JobModel();
                    job.ID = int.Parse(row["JobID"].ToString());
                    job.Title = row["JobTitle"].ToString();
                    job.Location = row["JobLocation"].ToString();
                    job.Salary = row["JobSalary"].ToString();
                    job.Description = row["JobDescription"].ToString();

                    msg.Data = job;
                    msg.Result = true;
                }
                else
                {
                    msg.Result = false;
                    msg.Data = new String(string.Format("Account does not exist.\nUsername/Password miss match").ToCharArray());
                }
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }

            return msg;   
        }


        public override Message SearchForAllData(Message msg)
        {
            mTable = new DataTable();
            mCommand = mConnection.CreateCommand();
            mCommand.CommandText = string.Format("SELECT * FROM Jobs");
            SqlDataAdapter mAdapter = new SqlDataAdapter(mCommand);

            try
            {
                mConnection.Open();
                mAdapter.Fill(mTable);
                mConnection.Close();

                if (mTable.Rows.Count != 0)
                {
                    msg.Result = true;
                    msg.Data = new List<JobModel>();

                    foreach (DataRow row in mTable.Rows)
                    {
                        JobModel job = new JobModel();
                        job.ID = int.Parse(row["JobID"].ToString());
                        job.Title = row["JobTitle"].ToString();
                        job.Location = row["JobLocation"].ToString();
                        job.Salary = row["JobSalary"].ToString();
                        job.Description = row["JobDescription"].ToString();
                        ((List<JobModel>)msg.Data).Add(job);
                    }
                }
                else
                {
                    msg.Result = false;
                    msg.Data = new String(string.Format("Account does not exist.\nUsername/Password miss match").ToCharArray());
                }
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }

            return msg;            
        }


        public override Message EditSpecificData(Message msg, Object data)
        {
            mTable = new DataTable();
            mCommand = mConnection.CreateCommand();
            mCommand.CommandText = string.Format("UPDATE Jobs SET JobID = '" + ((JobModel)data).ID + 
                                                            "', JobTitle = '" + ((JobModel)data).Title +
                                                            "', JobLocation = '" + ((JobModel)data).Location +
                                                            "', JobSalary = '" + ((JobModel)data).Salary +
                                                            "', JobDescription = '" + ((JobModel)data).Description +
                                                            "'WHERE JobID = '" + msg.Extra + "'");

            try
            {
                mConnection.Open();
                mCommand.ExecuteNonQuery();
                mConnection.Close();
                msg.Result = true;
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }
            return msg;
        }


        public override Message DeleteSpecificData(Message msg, Object data)
        {
            JobModel job = (JobModel)data;
            mCommand = mConnection.CreateCommand();

            mCommand.CommandText = string.Format("DELETE FROM Jobs Where JobTitle = '{0}'", job.Title);

            try
            {
                mConnection.Open();
                mCommand.ExecuteNonQuery();
                mConnection.Close();
                msg.Result = true;
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }

            return msg;
        }


        public override Message DeleteAllData(Message msg)
        {
            throw new NotImplementedException();
        }

        #endregion            
    }
}
